﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using MySql.Data.MySqlClient;

namespace DataBaseHelper
{
    public class DataBase
    {
        private string _server;
        private string _port;
        private string _userId;
        private string _password;
        private string _database;
        private string _baseTable;
        
        public DataBase(string server, string port, string userId, string password, string database, string basetable)
        {
            _server = server;
            _port = port;
            _userId = userId;
            _password = password;
            _database = database;
            _baseTable = basetable;
        }

        public void ExecuteNonQuery(string sqlcomm)
        {
            string connetionString = null;
            MySqlConnection cnn;
            MySqlCommand cmd;
            string sql = sqlcomm;
            //connetionString = "Data Source=" + _server + ";Initial Catalog=" + _database + ";User ID=" + _userId + ";Password=" + _password;
            connetionString = "Server=" + _server + ";Port=" + _port + ";Database=" + _database + ";Uid=" + _userId + ";Pwd=" + _password; 
            //sql = "Your SQL Statemnt Here";

            cnn = new MySqlConnection(connetionString);
            try
            {
                cnn.Open();
                cmd = new MySqlCommand(sql, cnn);
                cmd.ExecuteNonQuery();
                cmd.Dispose();
                cnn.Close();
            }
            catch (Exception ex)
            {
                Console.WriteLine("Błąd bazy danych!");
                Console.WriteLine(ex.Message);
            }
        }

        public void createCompanyView(string name, string channel_id)
        {
            string sql = "DROP VIEW IF EXISTS `" + name + "`;"  
                + "CREATE VIEW `" + name + "` AS SELECT * FROM `" + _baseTable 
                + "` WHERE channel_id = '" + channel_id + "'";
            ExecuteNonQuery(sql);
        }

        public void createComSalesView(string name, string channel_id)
        {
            string sql = "DROP VIEW IF EXISTS `" + name + "_sprzedaz`;"
                + "CREATE VIEW `" + name + "_sprzedaz` AS "
                + "(SELECT id, `time`, `sprzedaz_oferty1` AS 'sprzedaz_oferty', `sprzedaz_limit1` AS sprzedaz_limit,`sprzedaz_wolumen1` AS sprzedaz_wolumen FROM `"
                + name + "` WHERE sprzedaz_limit1 <> 0) "
                + " UNION ALL "
                + "(SELECT id,  `time`, `sprzedaz_oferty2` , `sprzedaz_limit2` ,`sprzedaz_wolumen2` FROM `"
                + name + "` WHERE sprzedaz_limit2 <> 0) "
                + " UNION ALL "
                + "(SELECT id,  `time`, `sprzedaz_oferty3` , `sprzedaz_limit3` ,`sprzedaz_wolumen3` FROM `"
                + name + "` WHERE sprzedaz_limit3 <> 0) "
                + " UNION ALL "
                + "(SELECT id,  `time`, `sprzedaz_oferty4` , `sprzedaz_limit4` ,`sprzedaz_wolumen4` FROM `"
                + name + "` WHERE sprzedaz_limit4 <> 0) "
                + " UNION ALL "
                + "(SELECT id,  `time`, `sprzedaz_oferty5` , `sprzedaz_limit5` ,`sprzedaz_wolumen5` FROM `"
                + name + "` WHERE sprzedaz_limit5 <> 0) "
                + "ORDER BY id ";
            ExecuteNonQuery(sql);

        }

        public void createComPurchaseView(string name, string channel_id)
        {
            string sql = "DROP VIEW IF EXISTS `" + name + "_kupno`;"
                + "CREATE VIEW `" + name + "_kupno` AS "
                + "(SELECT id, `time`, `kupno_oferty1` AS 'kupno_oferty', `kupno_limit1` AS kupno_limit,`kupno_wolumen1` AS kupno_wolumen FROM `"
                + name + "` WHERE kupno_limit1 <> 0) "
                + " UNION ALL "
                + "(SELECT id,  `time`, `kupno_oferty2` , `kupno_limit2` ,`kupno_wolumen2` FROM `"
                + name + "` WHERE kupno_limit2 <> 0) "
                + " UNION ALL "
                + "(SELECT id,  `time`, `kupno_oferty3` , `kupno_limit3` ,`kupno_wolumen3` FROM `"
                + name + "` WHERE kupno_limit3 <> 0) "
                + " UNION ALL "
                + "(SELECT id,  `time`, `kupno_oferty4` , `kupno_limit4` ,`kupno_wolumen4` FROM `"
                + name + "` WHERE kupno_limit4 <> 0) "
                + " UNION ALL "
                + "(SELECT id,  `time`, `kupno_oferty5` , `kupno_limit5` ,`kupno_wolumen5` FROM `"
                + name + "` WHERE kupno_limit5 <> 0) "
                + "ORDER BY id ";
            ExecuteNonQuery(sql);

        }

        public void createComValue(string name, string channel_id)
        {
            string sql = "DROP VIEW IF EXISTS `" + name + "_kurs`;"
                + "CREATE VIEW `" + name + "_kurs` AS (SELECT id, `transakcja_kurs1`, `time` FROM `" 
                + name + "` WHERE `transakcja_kurs1` <> 0) ORDER BY id";
            ExecuteNonQuery(sql);
        }
    }
}
